<?php

function getCurrentDraftRound($league) {
	
	$result = mysql_query("SELECT min(round) as currentRound from playerdraft where playerId is null and leagueId = $league");
	
	$row = mysql_fetch_array($result);
	$currentRound = $row['currentRound'];
	if ($currentRound == null) {
		$currentRound = 1;
	}
	
	
	return $currentRound;
}

function getPlayerDraftSQL() {
	$sql = "select pd.round, pd.pickNumber, pd.state, p.id as playerId, pd.teamId, pd.pickDateTime, pd.alreadypmed, t.coachName, t.usePreferences, p.firstName, p.surname, pd.playerPosition, p.playerType, p.team";
	$sql .= " from playerdraft pd inner join team t on pd.teamId = t.id left join player p on pd.playerId = p.id";
	
	return $sql;
}

function getDraft($league, $getAll) {
	$currentRound = getCurrentDraftRound($league);
	$sql = getPlayerDraftSQL() . " where " . "pd.leagueId = $league";
	if (!$getAll) {
		$sql .= " and (";
		if ($currentRound > 1 && $currentRound < 30) {
			$sql .= " round = " . ($currentRound - 1) . " or round = " . $currentRound . " or round = " . ($currentRound + 1) . ")";
		}
		else if ($currentRound == 1) {
			$sql .= " round = " . $currentRound . " or round = " . ($currentRound + 1) . ")";
		}
		else if ($currentRound == 30) {
			$sql .= " round = " . ($currentRound - 1) . " or round = " . $currentRound . ")";
		}
	}
	
	$result = mysql_query($sql);
	
	$array = array();
	$foundCurrentPick = false;	
	while($row = mysql_fetch_array($result)) {
		$array[] = getPlayDraftItem($row);
	}
	
	
	return $array;
}

function getRemainderDraft($league) {
	$sql = getPlayerDraftSQL() . " where " . "pd.leagueId = $league and playerId is null and state not in ('" . DraftPickStatus::Skipped . "', '" . DraftPickStatus::Skipped_Informed . "') order by pickNumber";
	
	$result = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$draftItem = getPlayDraftItem($row);
		$array[] = $draftItem;
	}
	
	
	return $array;
}

function getPlayDraftItem($row) {
	$draftItem = new PlayerDraft();
	$draftItem->teamId = $row['teamId'];
	$draftItem->coachName = $row['coachName'];
	$draftItem->round = $row['round'];
	$draftItem->pickNumber = $row['pickNumber'];
	$draftItem->pickDateTime = $row['pickDateTime'];
	$draftItem->state = $row['state'];
	$player = new Player();
	$player->id = $row['playerId'];
	$player->playerFirstName = $row['firstName'];
	$player->playerSurname = $row['surname'];
	$player->team = $row['team'];
	$player->playerType = $row['playerType'];
	$draftItem->player = $player;
	
	return $draftItem;
}

function getCurrentPick($league) {
	$currentRound = getCurrentDraftRound($league);
	$sql = getPlayerDraftSQL() . " where pd.leagueId = $league";
	
	$result = mysql_query($sql);
	
	$currentPickNumber = 0;
	while($row = mysql_fetch_array($result)) {
		if ($row['firstName'] == null && !isSkipped($row['state'])) {
			$currentPickNumber = $row['pickNumber'];
			break;
		}
	}
	
	
	return $currentPickNumber;
}

function getCurrentPickUser($leagueId) {
	$currentRound = getCurrentDraftRound($leagueId);
	$sql = getPlayerDraftSQL() . " where pd.leagueId = $leagueId";
	
	$result = mysql_query($sql);
	
	$currentPickCoachName = "";
	while($row = mysql_fetch_array($result)) {
		if ($row['firstName'] == null && !isSkipped($row['state'])) {
			if ($row['alreadypmed'] != true) {
				$currentPickCoachName = $row['coachName'];
				
				$sql = "update playerdraft set alreadypmed = true where pickNumber = " . $row['pickNumber'];
				mysql_query($sql);
			}
			break;
		}
	}
	
	
	return $currentPickCoachName;
}

function isYourPick($league, $coachName) {
	$currentRound = getCurrentDraftRound($league);
	$sql = getPlayerDraftSQL() . " where pd.leagueId = $league";
	
	$result = mysql_query($sql);
	
	$isYourPick = false;
	while($row = mysql_fetch_array($result)) {
		if ($row['firstName'] == null || isSkipped($row['state'])) {
			if ($row['coachName'] == $coachName) {
				$isYourPick = true;
			}
			
			if (!isSkipped($row['state'])) {
				break;
			}
		}
	}
	
	
	return $isYourPick;
}

function getPicks($teamId, $all, $league) {
	$currentRound = getCurrentDraftRound($league);
	$sql = getPlayerDraftSQL() . " where pd.teamId = $teamId";
	if (!$all) {
		$sql .= " and firstName is null"; 
	}
	
	$result = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$array[] = getPlayDraftItem($row);
	}
	
	
	return $array;
}

function skipMyPick($teamId, $leagueId) {
    $sql = "SELECT id, pickNumber, state from playerdraft where playerId is null and state not in ('" . DraftPickStatus::Skipped . "', '" . DraftPickStatus::Skipped_Informed . "') and teamId = $teamId order by pickNumber;";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$id = $row['id'];
	$pickNumber = $row['pickNumber'];
    $state = $row['state'];
	
	$sql = "UPDATE playerdraft set state = '" . DraftPickStatus::Skipped . "' where pickNumber = $pickNumber and leagueId = $leagueId;";
	mysql_query($sql);

    $sql = "update playerdraft set pickStartDateTime = now() where pickNumber = " . ($pickNumber + 1) . "  and leagueId = $leagueId;";
    mysql_query($sql);
}

function makeMyPick($teamId, $playerId, $playerPosition) {
	
	$sql = "SELECT leagueId from team where id = $teamId;";
	$teamResult = mysql_query($sql);
	$teamRow = mysql_fetch_array($teamResult);
	$leagueId = $teamRow['leagueId'];
	
	$currentPick = getCurrentPick($leagueId);
	
	$sql = "select count(*) as count from playerdraft pd inner join team t on pd.teamId = t.id where playerId = $playerId and t.leagueId = $leagueId";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	if ($row['count'] == 0) {
		$sql = "SELECT id, pickNumber, state from playerdraft where playerId is null and teamId = $teamId order by pickNumber;";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);
		$id = $row['id'];
		$pickNumber = $row['pickNumber'];
        $state = $row['state'];
        
        if ($pickNumber > $currentPick && $currentPick != 0) {
        	return false;
        }
	
		$sql = "UPDATE playerdraft set playerId = $playerId, pickDateTime = now(), state = '" . DraftPickStatus::Picked . "' where id = $id;";
		mysql_query($sql);

        if ($state == 'NOT_PICKED') {
        	$sql = "select pickNumber from playerdraft where leagueId = $leagueId and state = '" . DraftPickStatus::Not_Picked . "' order by pickNumber";
			$result = mysql_query($sql);
		   	$row = mysql_fetch_array($result);
            $sql = "UPDATE playerdraft set pickStartDateTime = now() where pickNumber = " . $row['pickNumber'] . " and leagueId = $leagueId";
            mysql_query($sql);
        }
		
		$sql = "INSERT INTO teamplayer (teamId, playerId, pickNumber, playerPosition) VALUES ($teamId, $playerId, $pickNumber, '$playerPosition');";
		
		mysql_query($sql);
		
			
		updatePreferences($playerId);
	
	    validateProposedTrades($leagueId);
	    
	    return true;
	}

	return false;
}

function setPreferences($selectedPlayers, $teamId) {
        $sql = "DELETE from preference where teamId = $teamId;";
	mysql_query($sql);
	$count = count($selectedPlayers);
	for ($i = 0; $i < $count; $i++) {
		$sql = "INSERT INTO preference (playerId, teamId, prefChoice, state) values (" . $selectedPlayers[$i] . "," . $teamId . "," . ($i+1) . ", '" . PreferenceStatus::Not_Taken . "');";
		mysql_query($sql);
	}
	
}

function usePreferences($teamId, $usePreferences) {
	$sql = "UPDATE team set usePreferences = $usePreferences where id = $teamId;";
	mysql_query($sql);
	
}

function getPreferences($teamId) {
	$sql = "SELECT p.id, p.firstName, p.surname, p.team from preference pr inner join player p on pr.playerId = p.id where teamId = $teamId and state = '" . PreferenceStatus::Not_Taken . "' order by prefChoice;";
	
	$result = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$player = new Player();
		$player->id = $row['id'];
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
		
		$array[] = $player;
	}
	
	
	return $array;
}

function getFirstPreference($teamId) {
	$sql = "SELECT p.id, p.firstName, p.surname, p.team from preference pr inner join player p on pr.playerId = p.id where teamId = $teamId and state = '" . PreferenceStatus::Not_Taken . "' order by prefChoice;";
	
	$result = mysql_query($sql);
	
	$row = mysql_fetch_array($result);
	$player = null;
	if ($row != null) {
		$player = new Player();
		$player->id = $row['id'];
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
	}
		
	
	return $player;
}

function hasUsedPreference($teamId) {
	$sql = "SELECT count(*) as count from preference where state = '" . PreferenceStatus::Used . "' and teamId = $teamId;";
	
	$result = mysql_query($sql);
	
	$row = mysql_fetch_array($result);
	$count = $row['count'];
		
	return ($count > 0);
}

function getDraftedPlayers($teamId) {
    $sql = "select p.id as playerId, p.firstName, p.surname, p.team, tp.playerPosition, tp.pickNumber, t.coachName, p.playerType, tp.isLti, tp.isLtiReplacement from teamplayer tp inner join team t on tp.teamId = t.id";
    $sql .= " inner join player p on tp.playerId = p.id where tp.teamId = $teamId and traded = false";
	$result = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$teamPlayer = getTeamPlayer($row);
		$array[] = $teamPlayer;
	}
	
	
	return $array;
}

function getUsePreferences($coachName, $teamId, $leagueId) {
	$sql = "SELECT value from settings where name = 'USE_PREFERENCES' and coachName = '$coachName' AND leagueId = $leagueId";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$usePreferences = false;
	if ($row['value'] == "ALL") {
		$usePreferences = true;
	}
	else if ($row['value'] == "ONE" && !hasUsedPreference($teamId)) {
		$usePreferences = true;
	}
	
	
	return $usePreferences;
} 

function checkSubsequentTeamPreferences($leagueId) {
	$remainderDraft = getRemainderDraft($leagueId);
	
	$count = count($remainderDraft); 
	for ($i = 0; $i < $count; $i++) {
		if (getUsePreferences($remainderDraft[$i]->coachName, $remainderDraft[$i]->teamId, $leagueId)) {
			$pref = getFirstPreference($remainderDraft[$i]->teamId);
			if ($pref != null) {
				if (makeMyPick($remainderDraft[$i]->teamId, $pref->id, "RES")) {
					preferenceUsed($pref->id, $remainderDraft[$i]->teamId);
				}
				else {
					break;
				}
			}
			else {
				break;
			}
		}
		else {
			break;
		}
	}
	
}

function updatePreferences($playerId) {
	$sql = "UPDATE preference set state = '" . PreferenceStatus::Taken . "' where playerId = $playerId;";
	
	mysql_query($sql);
}

function preferenceUsed($playerId, $teamId) {
	$sql = "UPDATE preference set state = '" . PreferenceStatus::Used . "' where playerId = $playerId and teamId = $teamId;";
	
	mysql_query($sql);
	
}

function getTakenUsedPreferences($teamId, $forAdmin) {
	$sql = "select firstName, surname, team, state from preference pr inner join player p on pr.playerId = p.id where pr.teamId = $teamId and (state = '" . PreferenceStatus::Taken . "' OR state = '" . PreferenceStatus::Used . "')";
	$result = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$player = new PrefChanged();
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
		$player->state = $row['state'];
		
		$array[] = $player;
	}
	
	if (!$forAdmin) {
		$sql = "delete from preference where (state = '" . PreferenceStatus::Taken . "' or state = '" . PreferenceStatus::Used . "') and teamID = $teamId";
		mysql_query($sql);
	}
	
	
	return $array;
}

function getSkippedPicks($teamId) {
	$sql = "SELECT count(*) as count from playerdraft where state in ('" . DraftPickStatus::Skipped . "', '" . DraftPickStatus::Skipped_Informed . "') and teamId = $teamId;";

	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$hasSkippedPicks = false;
	
	if ($row['count'] > 0) {
		$hasSkippedPicks = true;
	}	
		
	
	return $hasSkippedPicks;
}

function skipPicks($leagueId, $settings) {
	date_default_timezone_set(@date_default_timezone_get());
	$sql = "select value from settings where name = 'TIME_LIMIT' and leagueId = $leagueId";
	
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	if ($row['value'] == 'ON') {
		$sql = "select pickStartDateTime, teamId from playerdraft where leagueId = $leagueId and state = '" . DraftPickStatus::Not_Picked . "' order by pickNumber";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);
        $pickStartDateTime = strtotime($row['pickStartDateTime']);
		$now = time();
		
        $expireTime = $settings->timeLength;

        if ($row['teamId'] != null) {
	        $sql = "select count(*) as count from playerdraft where teamId = " . $row['teamId'] . " and (state = '" . DraftPickStatus::Skipped . "' or state = '" . DraftPickStatus::Skipped_Informed . "')";
			$result = mysql_query($sql);
			$row = mysql_fetch_array($result);
	        if ($row['count'] == 1) {
	           $expireTime *= 0.5;
	        }
	        else if ($row['count'] == 2) {
	           $expireTime *= 0.25;
	        }
	        else if ($row['count'] > 2) {
	           $expireTime *= 0;
	        }
	
	        $pickExpireDate = strtotime("+" . $expireTime . " hours", $pickStartDateTime);
	
			if ($now > $pickExpireDate) {
				$sql = "select pickNumber, pickDateTime from playerdraft where leagueId = $leagueId and state = '" . DraftPickStatus::Not_Picked . "' order by pickNumber";
				$result = mysql_query($sql);
				$row = mysql_fetch_array($result);
	                        $pickNumber = $row['pickNumber'];
				$sql = "UPDATE playerdraft set state = '" . DraftPickStatus::Skipped . "' where pickNumber = " . $pickNumber . " and leagueId = $leagueId";
				mysql_query($sql);
	
	            $sql = "update playerdraft set pickStartDateTime = now() where pickNumber = " . ($pickNumber + 1) . " and leagueId = $leagueId";
	            mysql_query($sql);
	
	            checkSubsequentTeamPreferences($leagueId);
			}
        }
	}	
		
}

function getMadeAndSkippedPicks($leagueId) {
	$sql = getPlayerDraftSQL() . " where " . "pd.leagueId = $leagueId and state in ('" . DraftPickStatus::Picked . "', '" . DraftPickStatus::Skipped . "')";
	
	$result = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$array[] = getPlayDraftItem($row);
	}	
		
	
	return $array;
}

function isPickValid($pickNumber, $teamId) {
	$sql = "select state, teamId from playerdraft where pickNumber = $pickNumber";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
        $returnResult = true;
        if ($row['state'] <> DraftPickStatus::Not_Picked || $row['teamId'] != $teamId) {
           $returnResult = false;
        }
		
	
	return $returnResult;
}

function updateDraftToInformed($leagueId) {
	$sql = "UPDATE playerdraft set state = '" . DraftPickStatus::Picked_Informed . "' where state = '" . DraftPickStatus::Picked . "' and leagueId = $leagueId;";
	mysql_query($sql);
	
	$sql = "UPDATE playerdraft set state = '" . DraftPickStatus::Skipped_Informed . "' where state = '" . DraftPickStatus::Skipped . "' and leagueId = $leagueId;";
	mysql_query($sql);
}

?>
